Global Index

1. Function Description

The global index means that TcaplusDB and the index system are independent of each other. The data in TcaplusDB is synchronized to the index system through asynchronous synchronization; Global index is also called distributed index;

Global indexes have the following features:

(1) The global index is a quasi-real-time index system. When querying the global index, the newly updated data in the TcaplusDB table cannot be queried in real time. Under normal circumstances, the delay between the newly updated data and the data that can be queried through the index is seconds, most of which is within one second;

(2) The fields created with the global index can support range queries, fuzzy queries and aggregate queries for these fields;

(3) Currently, global indexes can only be created for Generic tables;

(4) Global indexes can only be created for first-level fields of the table (including Key fields and Value fields);

(5) Global indexes can only be created for fields of simple types. Simple types include: bool, enum, int8, uint8, int16, uint16, int32, uint32, int64, uint64, float, double, string(string);

(6) Global indexes support dynamic creation and modification. Global indexes can be added, deleted or modified at any time. When modifying the global index, for example, adding an index field will not affect the app's query of the global index before modification;

The architecture between TcaplusDB and the global index is shown in the following figure:

2. Application Scenarios

Global indexes can be created for some level-1 field range queries, fuzzy queries, or aggregate queries.

For example, in a user table, there are two first level fields: score and grade. Now we want to realize the following requirements:

Query for users whose score is greater than a given value, or for users whose grade is greater than a given value, or for both users whose score is greater than a given value and whose grade is greater than a given value.

At this time, you can create a global index for the score and grade fields and then run a global index query.

3. Instructions

3.1. Creating An Index

Global indexes need to be created on the page. For details about the creation process, see Create a Distributed Index (Global Index).

When creating an index, it needs to update the historical data in the index system, which may take a long time. Global index access is available only after the transaction that created the index completes successfully.

3.2. Modifying An Index

The entry for modifying an index is the same as the entry for creating an index. For details, see 3.1. Creating An Index.

When modifying an index, it needs to update the historical data in the index system, which may take a long time. The global index of the modified field can be queried only after the index modification transaction is successfully completed.

Note: During the index modification process, the access to the previous global index fields will not be affected. After the index modification is completed, only the fields for which the global index has been created can be accessed;

3.3. Delete Indexes

The entry for deleting an index is the same as the entry for creating an index. For details, see 3.1. Creating An Index.

After the global index is deleted, global index access is no longer available.

3.4 Querying An Index

TcaplusDB provides SQL query statements for global index queries. The fields in the SQL query condition must be the fields with global indexes. In addition, if it is an aggregated query, the fields of the aggregated query must also be fields that have a global index.

3.4.1. Global index query interface

For details, see the descriptions of SDKS and API, TcaplusDB SDK & API.

3.4.2. Supported SQL query statements

3.4.2.1. Condition query

Support =, >, >=, <, <=, !=, between, in, not in, like, not like, and, or, for example:

select * from table where a > 100 and b < 1000;

select * from table where a between 1 and 100 and b < 1000;

select * from table where str like "test";

select * from table where a > 100 or b < 1000;

Note: For the query between a and b, the corresponding query range is [a, b]. For example, between 1 and 100 contains the values 1 and 100, that is, the query range is [1,100].

Note: The like query supports fuzzy matching. The "%" wildcard matches 0 or more characters while the "_" wildcard matches 1 character.

3.4.2.2. Pagination query

Pagination queries that support limit offset, such as:

select * from table whre a > 100 limit 100 offset 0;

Note: The limit must be used with offset, that is, limit 1 or limit 0,1 are not supported.

3.4.2.3. Aggregate query

Currently, aggregate queries that are supported include: sum, count, max, min, avg, for example:

select sum(a), count(*), max(a), min(a), avg(a) from table where a > 1000;

Note: Aggregate queries do not support limit offsets, that is, limit offsets do not take effect.

Note: Currently, only count supports distinct, that is, select count (distinct (a)) from the table where a > 1000; distinct is not supported in other cases.

3.4.2.4. Query of partial fields

It supports query the values of partial fields, such as:

select a, b from table where a > 1000;

For the pb table, it also supports querying the values of nested fields, such as: select field1.field2.field3, a, b from table where a > 1000;

3.4.3. SQL query statements that are not supported

3.4.3.1. Mixed use of aggregate query and non aggregate query is not supported

select *, a, b from table where a > 1000;

select sum(a), a, b from table where a  > 1000;

select count(*), * from table where a  > 1000;

3.4.3.2. Order by query is not supported

select * from table where a > 1000 order by a;

3.4.3.3. Group by query is not supported

select * from table where a > 1000 group by a;

3.4.3.4. Having query is not supported

select sum(a) from table where  a > 1000 group by a having sum(a) > 10000;

3.4.3.5. Joint query of multiple tables is not supported

select * from table1 where table1.a > 1000 and table1.a = table2.b;

3.4.3.6. Nested select query is not supported

select * from table where a > 1000 and b in (select b from table where b < 5000);

3.4.3.7. Alias is not supported

select sum(a) as sum_a from table where a > 1000;

3.4.3.8. Other queries that are not supported

(1) The join query is not supported.

(2) The union query is not supported.

(3) Queries such as select a + b from table where a > 1000 are not supported.

(4) Queries such as select * from table where a + b > 1000 are not supported.

(5) Queries such as select * from table where a >= b are not supported.

(6) Other queries not mentioned are not supported.

4. Notations

(1) The total length of the primary key fields in the table definition should not exceed 256 bytes. Otherwise, the global index function will be abnormal.

(2) If there are too many records that meet the query condition, the query will take a long time (seconds or more).

(3) Each query returns a maximum of 3000 records. If the number of records exceeds 3000, you need to query them with the limit offset multiple times. If limit + offset is greater than 10000 and the number of records that meet the query conditions is indeed greater than 10000, the global index directly reports an error. That is, the global index can return a maximum of 10000 records for the same query condition.

(4) Records configured for data eviction will be synchronously deleted from the global index system upon expiration.

Distributed Index (Global Index)